package com.baijiaoxi.utils.excel;

import com.baijiaoxi.common.model.base.CommonError;
import com.baijiaoxi.common.model.base.CoreException;
import com.baijiaoxi.common.model.excel.ExcelHeaderAnalyser;
import com.baijiaoxi.common.model.excel.ExcelImportResult;
import com.baijiaoxi.common.model.excel.ExcelImportRowHandler;
import com.baijiaoxi.common.model.excel.HeaderCellInfo;
import com.baijiaoxi.common.utils.jdk.Strings;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicBoolean;

@Slf4j
public class ExcelImportHelper {
    private static String EXTENSION_XLS = "xls";
    private static String EXTENSION_XLSX = "xlsx";
    private static SimpleDateFormat SDF_FULL = new SimpleDateFormat("yy-MM-dd HH:mm:ss");//小写的mm表示的是分钟
    private static SimpleDateFormat SDF_NORMAL = new SimpleDateFormat("yy-MM-dd HH:mm");//小写的mm表示的是分钟
    private static SimpleDateFormat SDF_SHORT = new SimpleDateFormat("yy-MM-dd");//小写的mm表示的是分钟

    /**
     * excel转换成对象list
     *
     * @param rowStart
     * @param fileExtension
     * @param inputStream
     * @param map
     * @param clas
     * @return
     * @throws IOException
     */
    public static ExcelImportRes transferExcelToEntity(int rowStart, List resList, String fileExtension,
                                                       InputStream inputStream, Map<String, Integer> map,
                                                       Map<String, Map<String, String>> refectMap, Class clas)
            throws Exception {
        // 获取workbook对象
        Workbook workbook = null;
        ExcelImportRes excelImportRes = new ExcelImportRes();
        try {
            //构造excel数据对象
            workbook = ExcelUtil.getWorkbook(fileExtension, inputStream);
            //获取成员变量
            Field[] fields = clas.getDeclaredFields();
            //获取成员变量Map<name,type>
            Map<String, Class> fieldMap = new HashMap();
            for (int i = 0; i < fields.length; i++) {
                fieldMap.put(fields[i].getName(), fields[i].getType());
            }

            for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
                Sheet sheet = workbook.getSheetAt(numSheet);
                if (sheet == null) {
                    continue;
                }
                int firstRowIndex = sheet.getFirstRowNum();//初始行
                int lastRowIndex = sheet.getLastRowNum();//末行
                //int counter = sheet.getPhysicalNumberOfRows(); //获得总行数
                if (firstRowIndex == lastRowIndex)
                    continue;
                List<Integer> errorRowIndexList = new ArrayList<>();//记录错误行索引值
                List<Integer> successRowIndexList = new ArrayList<>();//记录成功行索引值
                for (int rowIndex = rowStart; rowIndex <= lastRowIndex; rowIndex++) {
                    try {
                        Row currentRow = sheet.getRow(rowIndex);
                        if (currentRow != null) {
                            Object entity = transferRowToObject(map, currentRow, clas, fieldMap, refectMap);
                            if (entity != null) {
                                //空行自动跳过
                                successRowIndexList.add(rowIndex);
                                resList.add(entity);
                            } else log.error(rowIndex + "行为空行");
                        }
                    } catch (Exception e) {
                        errorRowIndexList.add(rowIndex);
                    }
                }
                if (!errorRowIndexList.isEmpty()) {
                    excelImportRes.setExistsError(true);
                    successRowIndexList.stream().forEach(successRowIndex -> sheet.removeRow(sheet.getRow(successRowIndex)));
                    ByteArrayOutputStream out = new ByteArrayOutputStream();
                    workbook.write(out);
                    excelImportRes.setFilebyte(out.toByteArray());
                }
            }
        } catch (Exception e) {
            throw e;
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            inputStream.close();
        }
        return excelImportRes;
    }

    public static ExcelImportResult invoke(String fullFileName, String fileExtension, ExcelHeaderAnalyser excelHeaderAnalyser, ExcelImportRowHandler excelImportRowHandler) throws Exception {
        return invoke(new FileInputStream(fullFileName), fileExtension, excelHeaderAnalyser, excelImportRowHandler);
    }

    public static ExcelImportResult invoke(InputStream inputStream, String fileExtension, ExcelImportRowHandler excelImportRowHandler) throws Exception {
        return invoke(inputStream, fileExtension, null, excelImportRowHandler);
    }

    public static <T, E> ExcelImportResult invoke(InputStream inputStream, String fileExtension,
                                                  ExcelHeaderAnalyser excelHeaderAnalyser,
                                                  ExcelImportRowHandler<T> excelImportRowHandler) throws Exception {
        // 获取workbook对象
        ExcelImportResult result = new ExcelImportResult();
        Workbook workbook = null;
        try {
            //构造excel数据对象
            workbook = ExcelUtil.getWorkbook(fileExtension, inputStream);
            for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
                Sheet sheet = workbook.getSheetAt(numSheet);
                if (sheet == null) {
                    continue;
                }
                int firstRowIndex = sheet.getFirstRowNum();//初始行
                int lastRowIndex = sheet.getLastRowNum();//末行
                log.debug("初始行：{},末行：{}", firstRowIndex, lastRowIndex);
                //int counter = sheet.getPhysicalNumberOfRows(); //获得总行数
                if (firstRowIndex == lastRowIndex)
                    continue;

                //获取header
                List<HeaderCellInfo> headerHeaderCellInfoList = new ArrayList<>();
                try {
                    Row headerRow = sheet.getRow(firstRowIndex);
                    for (int tempHeaderIndex = headerRow.getFirstCellNum(); tempHeaderIndex <= headerRow.getLastCellNum(); tempHeaderIndex++) {
                        Cell headerCell = headerRow.getCell(tempHeaderIndex);
                        if (headerCell == null)
                            continue;
                        //隐藏的列不读取
                        if (sheet.getColumnWidth(tempHeaderIndex) <= 0)
                            continue;
                        String headerTitle = headerCell.getStringCellValue();
                        //没有标题的列不读取
                        if (Strings.isBlank(headerTitle))
                            continue;
                        HeaderCellInfo headerCellInfo;
                        if (excelHeaderAnalyser == null)
                            headerCellInfo = new HeaderCellInfo(tempHeaderIndex, headerTitle);
                        else
                            headerCellInfo = excelHeaderAnalyser.getHeaderCellInfo(tempHeaderIndex, headerTitle);
                        if (headerCellInfo != null)
                            headerHeaderCellInfoList.add(headerCellInfo);
                    }
                } catch (Exception e) {
                    throw CoreException.create(CommonError.analyseSheetHeaderFail, e);
                }
                if (excelHeaderAnalyser != null)
                    excelHeaderAnalyser.afterAnalyseHeader(headerHeaderCellInfoList);

                List<ErrorRow> errorRowList = new ArrayList<>();//记录错误行索引值
                List<Integer> successRowIndexList = new ArrayList<>();//记录成功行索引值
                for (int rowIndex = firstRowIndex + 1; rowIndex <= lastRowIndex; rowIndex++) {
                    log.debug("开始处理第{}行", rowIndex);
                    try {
                        Row currentRow = sheet.getRow(rowIndex);
                        if (currentRow == null) {
                            log.info(rowIndex + "数据行为空行");
                            continue;
                        }
                        T entity = excelImportRowHandler.newInstance();
                        AtomicBoolean allEmpty = new AtomicBoolean(true);
                        headerHeaderCellInfoList.forEach(headerHeaderCellInfo ->
                                {
                                    String value = ExcelUtil.getCellValue(currentRow.getCell(headerHeaderCellInfo.getHeaderIndex()));
                                    if (value.equals(""))
                                        allEmpty.set(false);
                                    excelImportRowHandler.setCellValue(entity, headerHeaderCellInfo, value);
                                }
                        );
                        if (allEmpty.get())
                            log.error("第{}行是空行", rowIndex);
                        else {
                            excelImportRowHandler.afterAnalyseRow(entity, headerHeaderCellInfoList);
                            successRowIndexList.add(rowIndex);
                        }
                    } catch (CoreException e) {
                        ErrorRow errorRow = new ErrorRow(rowIndex, e.getMessage());
                        log.error("处理第{}行Core异常,{}", rowIndex, errorRow);
                        errorRowList.add(errorRow);
                    } catch (Exception e) {
                        ErrorRow errorRow = new ErrorRow(rowIndex, e.getMessage());
                        log.error("处理第{}行异常,{}", rowIndex, errorRow);
                        errorRowList.add(errorRow);
                    }
                }
                result.setTotal(result.getTotal() + errorRowList.size() + successRowIndexList.size());
                result.setError(result.getError() + errorRowList.size());
                if (!errorRowList.isEmpty()) {
                    int errorCellIndex = headerHeaderCellInfoList.size();
                    Row headerRow = sheet.getRow(0);
                    Cell headerCell = headerRow.getCell(errorCellIndex);
                    if (headerCell == null)
                        headerCell = headerRow.createCell(errorCellIndex);
                    headerCell.setCellValue("导入失败原因");
                    for (ErrorRow errorRow : errorRowList) {
                        Row row = sheet.getRow(errorRow.getRowIndex());
                        Cell cell = row.getCell(errorCellIndex);
                        if (cell == null)
                            cell = row.createCell(errorCellIndex);
                        cell.setCellValue(errorRow.getErrorMsg());
                    }
                    int removeCount = 0;
                    for (int i = 0; i < successRowIndexList.size(); i++) {
                        int rowIndex = successRowIndexList.get(i);
                        log.debug("尝试移除行{}", rowIndex);
                        int nowIndex = rowIndex;
                        try {
                            if (removeCount > 0)
                                nowIndex = rowIndex - removeCount;
                            sheet.shiftRows(nowIndex + 1, lastRowIndex, -1);
                            removeCount += 1;
                        } catch (Exception e) {
                            log.error("行移除失败", e);
                        }
                    }
                    ByteArrayOutputStream out = new ByteArrayOutputStream();
                    workbook.write(out);
                    result.setExcel(out.toByteArray());
                }
            }
        } catch (Exception e) {
            throw e;
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            inputStream.close();
        }
        return result;
    }

    /**
     * 根据成员变量类型动态转换
     *
     * @param s
     * @param clas
     * @return
     */
    public static Object getValue(String s, Class clas) throws Exception {
        try {
            if (clas.getName().equals("java.lang.Long")) {
                return Long.valueOf(s);
            }
            if (clas.getName().equals("java.lang.Integer")) {
                return Integer.valueOf(s);
            }
            if (clas.getName().equals("java.lang.Double")) {
                return Double.valueOf(s);
            }
            if (clas.getName().equals("java.lang.Boolean")) {
                return Boolean.valueOf(s);
            }
            if (clas.getName().equals("java.util.Date")) {
                try {
                    return SDF_FULL.parse(s);
                } catch (Exception e) {
                    try {
                        return SDF_NORMAL.parse(s);
                    } catch (Exception e1) {
                        return SDF_SHORT.parse(s);
                    }
                }
            }
            if (clas.getName().equals("java.lang.Byte")) {
                return Byte.valueOf(s);
            } else return s == null ? "" : s;
        } catch (ParseException p) {
            p.printStackTrace();
            throw p;
        }
    }

    private static Object transferRowToObject(Map<String, Integer> map, Row currentRow, Class clas, Map<String, Class> fieldMap, Map<String, Map<String, String>> refectMap) throws Exception {
        //空行跳过
        Object entity = null;
        boolean notNull = false;
        if (currentRow != null) {
            entity = clas.newInstance();//存储实体
            for (String key : map.keySet()) {
                //首字母大写
                char[] cs = key.toCharArray();
                cs[0] -= 32;
                //获取set方法名
                String setMethodName = "set" + (String.valueOf(cs));
                Class fieldType = fieldMap.get(key);
                Method setMethod = clas.getMethod(setMethodName, fieldType);
                //获取参数类型
                Object[] args = new Object[1];
                String val = ExcelUtil.getCellValue(currentRow.getCell(map.get(key)));
                if (val != null && !val.trim().isEmpty())
                    notNull = true;
                if (refectMap != null && refectMap.containsKey(key))
                    val = refectMap.get(key).get(val);
                if (val == null || val.isEmpty())
                    continue;
                args[0] = getValue(val, fieldType);
                setMethod.invoke(entity, args[0]);
            }
        }
        if (notNull)
            return entity;
        else return null;
    }
}
